package org.eclipse.wtp.puzzled;

import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class MySQLAccess {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public MySQLAccess() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager
					.getConnection("jdbc:mysql://localhost/puzzled?"
							+ "user=charlie&password=wacko180");			
		} catch (Exception e) {
			throw e;
		}
	}
	
	public User authenticate(String username, String password) throws Exception {
		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM users WHERE username='" + username + "' LIMIT 1");
			resultSet.first();
			String pwd = resultSet.getString("password");
			if (password.equals(pwd)) {
				String fname = resultSet.getString("firstname");
				String lname = resultSet.getString("lastname");
				String email = resultSet.getString("email");
				int points = Integer.parseInt(resultSet.getString("points"));
				User tmp = new User(fname, lname, email, username);
				tmp.setPoints(points);
				return tmp;
			} else return null;
		} catch (Exception e) {
			throw e;
		}
	}
	
	public User createUser(String fname, String lname, String email, String user, String pass) throws Exception {
		try {
			preparedStatement = connect.prepareStatement("INSERT INTO users VALUES (null, ?, ?, ?, ?, ?, default)");
			preparedStatement.setString(1, fname);
			preparedStatement.setString(2, lname);
			preparedStatement.setString(3, email);
			preparedStatement.setString(4, user);
			preparedStatement.setString(5, pass);
			
			if (preparedStatement.execute()) {
				User tmp = new User(fname, lname, email, user);
				return tmp;
			} else return null;
		} catch (Exception e) {
			throw e;
		}
	}
	
	public void updatePoints(String u, int p) throws Exception {
		System.out.println("updating points to: " + p + " for user: " + u);
		try {
			preparedStatement = connect.prepareStatement("UPDATE users SET points=? WHERE username=?");
			preparedStatement.setInt(1, p);
			preparedStatement.setString(2, u);
			preparedStatement.execute();
		} catch (Exception e) {
			throw e;
		}
	}
	
	public ArrayList<User> getHighScores() throws Exception {
		ArrayList<User> users = new ArrayList<User>();
		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM users ORDER BY points DESC LIMIT 5");
			
			while (resultSet.next()) {
				String fname = resultSet.getString("firstname");
				String lname = resultSet.getString("lastname");
				String email = resultSet.getString("email");
				String user = resultSet.getString("username");
				int points = resultSet.getInt("points");
				User tmp = new User(fname, lname, email, user);
				tmp.setPoints(points);
				users.add(tmp);
			}
			return users;
		} catch (Exception e) {
			throw e;
		}
	}

	// You need to close the resultSet
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}
